USE [NGroup_VTL_DB]
GO

/****** Object:  StoredProcedure [dbo].[GetNewsListForAdministration]    Script Date: 01/12/2014 19:08:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNewsListForAdministration]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetNewsListForAdministration]
GO

USE [NGroup_VTL_DB]
GO

/****** Object:  StoredProcedure [dbo].[GetNewsListForAdministration]    Script Date: 01/12/2014 19:08:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Bui Thanh Nam
-- Create date: 20131229
-- Description:	Get product list in admin page
-- =============================================
CREATE PROCEDURE [dbo].[GetNewsListForAdministration]
	@PageIndex			INT = 1,
	@PageSize			INT = 12,	
	@Author				NVARCHAR(50) = NULL,
	@NewsTypeID			INT = -1,
	@TotalRows			INT = 0 OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE 
			@BeginOffset	INT,
			@EndOffset		INT			

	DECLARE @TAB_NEWS_ORDERED TABLE 
	(
		[RowIndex]			INT
		,[ID]				INT
		,[UniqueString]		NVARCHAR(500)		  
		,[Title]			NVARCHAR(500)		
		,[CreatedAt]		DATETIME
		,[CreatedBy]		NVARCHAR(50)
		,[TypeID]			INT
	)
	
    ;WITH TAB_NEWS_ORDERED AS
	(
		SELECT 
			   ROW_NUMBER() OVER (ORDER BY [dbo].[News].[CreatedAt] DESC) AS [RowIndex],
			   [dbo].[News].[ID]			  
			  ,[dbo].[News].[UniqueString]			  
			  ,[dbo].[News].[Title]
			  ,[dbo].[News].[CreatedAt]
			  ,[dbo].[News].[CreatedBy]
			  ,[dbo].[News].[TypeID]			  	  
		FROM [dbo].[News] WITH (NOLOCK) INNER JOIN [dbo].[GenericTypes] WITH (NOLOCK)
			ON [dbo].[News].[TypeID] = [dbo].[GenericTypes].[ID]
		WHERE 
			([dbo].[News].[CreatedBy] = @Author OR @Author IS NULL)
			/*AND ([dbo].[News].[TypeID] = @NewsTypeID OR @NewsTypeID = -1)*/
			AND [dbo].[GenericTypes].[UniqueString] = 'tin-tuc'	
			AND [dbo].[News].[IsActive] <> 0
			AND [dbo].[GenericTypes].[IsActive] <> 0
	)
	INSERT INTO @TAB_NEWS_ORDERED
	SELECT *
	FROM TAB_NEWS_ORDERED
	
	SELECT @TotalRows = MAX([RowIndex])
	FROM @TAB_NEWS_ORDERED

	SELECT @BeginOffset = (@PageSize * (@PageIndex - 1)) + 1, 
		   @EndOffset = (@BeginOffset - 1) + @PageSize;

	SELECT * 
	FROM @TAB_NEWS_ORDERED
	WHERE [RowIndex] BETWEEN @BeginOffset AND @EndOffset	

END







GO

